package com.notemanagement.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.notemanagement.bean.OrderDetailInfo;
import com.notemanagement.ui.Order;

import java.security.PublicKey;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by 金 on 2015/1/17.
 */
public class SQLiteHelper extends SQLiteOpenHelper{

    private static final  String databaseName = "noteData.db";
    private static final  int version = 1;
    private static SQLiteHelper helper;
    private static SQLiteDatabase db;

    public SQLiteHelper(Context context) {
        super(context, databaseName, null, version);
    }

    @Override
    public void onCreate(android.database.sqlite.SQLiteDatabase db) {
        String sql = "create table product(id integer primary key autoincrement," +
                "name varchar(20) not null);";
        db.execSQL(sql);

        ContentValues cv = new ContentValues();
        cv.put("name", "");
        db.insert("product", null, cv);

        sql = "create table orderlist " +
                "(id integer primary key autoincrement," +
                "name varchar(20) not null," +
                "phone varchar(11) ," +
                "address varchar(50)," +
                "product1 varchar(20),num1 int," +
                "product2 varchar(20),num2 int," +
                "product3 varchar(20),num3 int," +
                "product4 varchar(20),num4 int," +
                "product5 varchar(20),num5 int," +
                "datetime varchar(8))";
        db.execSQL(sql);

    }

    @Override
    public void onUpgrade(android.database.sqlite.SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public  static SQLiteHelper getInstance(Context context){
        if(helper == null) {
            helper = new SQLiteHelper(context);
            db = helper.getWritableDatabase();
        }

        return  helper;
    }

    public List<OrderDetailInfo> queryOrderList(){
        List<OrderDetailInfo> list = new ArrayList<OrderDetailInfo>();
        String sql = "select * from  orderlist";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            OrderDetailInfo info = new OrderDetailInfo();
            info.id = cursor.getInt(0); //获取第一列的值,第一列的索引从0开始
            info.name = cursor.getString(1); //name
            info.phone = cursor.getString(2);//phone
            info.address = cursor.getString(3); //address
            info.materials.add(cursor.getString(4));//物品1
            info.materialNum.add(cursor.getInt(5));//物品1
            info.materials.add(cursor.getString(6));//物品2
            info.materialNum.add(cursor.getInt(7));//物品2
            info.materials.add(cursor.getString(8));//物品3
            info.materialNum.add(cursor.getInt(9));//物品3
            info.materials.add(cursor.getString(10));//物品4
            info.materialNum.add(cursor.getInt(11));//物品4
            info.materials.add(cursor.getString(12));//物品5
            info.materialNum.add(cursor.getInt(13));//物品5
            info.date = cursor.getString(14);//日期
            list.add(info);
        }

        return list;
    }

    public ArrayList<String> queryProductItems(){
        String sql = "select name from product;";
        Cursor cursor = db.rawQuery(sql, null);
        ArrayList<String> list = new ArrayList<String>();
        while (cursor.moveToNext()){
            list.add(cursor.getString(0));
        }

        return  list;
    }

    public boolean insertProductItem(String item){

        Cursor cursor = db.query("product", new String[]{"name"}, "name=?", new String[]{item}, null, null, null, null);
        if(cursor.getCount() > 0)
            return  true;

        ContentValues cv = new ContentValues();
        cv.put("name", item);
        long id = db.insert("product", null, cv);
        if(id >= 0)
            return  true;
        return  false;

    }

    public long insertOrderDetail(OrderDetailInfo info){
        ContentValues cv = new ContentValues();
        cv.put("name", info.name);
        cv.put("phone", info.phone);
        cv.put("address", info.address);
        cv.put("product1", info.materials.get(0));
        cv.put("num1", info.materialNum.get(0));
        cv.put("product2", info.materials.get(1));
        cv.put("num2", info.materialNum.get(1));
        cv.put("product3", info.materials.get(2));
        cv.put("num3", info.materialNum.get(2));
        cv.put("product4", info.materials.get(3));
        cv.put("num4", info.materialNum.get(3));
        cv.put("product5", info.materials.get(4));
        cv.put("num5", info.materialNum.get(4));
        cv.put("datetime", info.date);

        db.insert("product", null, cv);
        long id = db.insert("orderlist", null, cv);

        return  id;
    }

    public  int deleteItem(long id){
        return db.delete("orderlist", "id=?", new String[]{id + ""});
    }
}
